﻿using Dapper;
using OMG.Fill.Potato.Model;
using OMG.Fill.Potato.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.Linq;
namespace OMG.Fill.Potato.Services
{
    /// <summary>
    /// 马铃薯技术推广机构及人员情况统计表通用操作类
    /// </summary>
    public class PotPromotionServices: PotPromotions
    {
        public Pager<List<PotPromotionEntity>> GetPageList(PotPromotionQuery query)
        {
            Pager<List<PotPromotionEntity>> obj = new Pager<List<PotPromotionEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_PotPromotion where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<PotPromotionEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取马铃薯技术推广机构及人员情况统计表分页列表失败", ex);
                return obj;
            }
        }

        public ResResult<PromotionFillModel> GetModel(string AreaCode, string FillInYear)
        {
            ReportServices reportService = new ReportServices();
            ResResult<PromotionFillModel> obj = new ResResult<PromotionFillModel>();
            PromotionFillModel model = new PromotionFillModel();
            int oldFillInYear = Convert.ToInt32(FillInYear) - 1;
            string strSql = "select t1.* from T_PotPromotion t1 join T_Report t2 on t1.ReportId = t2.Id";
            strSql += "  where t2.AreaCode = @AreaCode and t2.FillInYear = @FillInYear and ReportCode = 'PotPromotion' ";
            if (reportService.CheckRepCreate("PotPromotion", AreaCode, FillInYear))
            {
                obj.status = DefaultKey.ErrorResult;
                obj.model = model;
                obj.message = FillInYear + "年的全年生产情况表没有生成！";
            }
            else
            {
               ReportEntity  reportModel = reportService.GetReportView("PotPromotion", AreaCode, FillInYear);
                model.ReportStatus = reportModel.Status;
                model.ReportId = reportModel.Id;
                model.Opinion = reportModel.Opinion;
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    if (reportService.CheckRepCount(model.ReportId, "T_PotPromotion"))
                    {
                        PotPromotionEntity entity = new PotPromotionEntity();
                        entity.ReportId = model.ReportId;
                        AddInfo(entity);
                    }
                    model.DataModel = dbCon.Query<PotPromotionEntity>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
                    if (!reportService.CheckRepCreate("PotPromotion", AreaCode, oldFillInYear.ToString()))
                    {
                        model.OldDataModel = dbCon.Query<PotPromotionEntity>(strSql, new { AreaCode = AreaCode, FillInYear = oldFillInYear.ToString() }).FirstOrDefault();
                    }
                }
                obj.model = model;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "数据获取成功！";
            }
            return obj;
        }

        public ResResult<List<PromotionStatisModel>> getStatisListData(string AreaCode, string FillInYear)
        {
            ResResult<List<PromotionStatisModel>> obj = new ResResult<List<PromotionStatisModel>>();
            List<PromotionStatisModel> list = new List<PromotionStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                var citylist = areaServices.GetPrefectureList();
                PromotionStatisModel totalModel = new PromotionStatisModel();
                totalModel.AreaCode = AreaCode;
                totalModel.AreaName = "合计";
                totalModel.tag = 2;
                totalModel.InstituNum = 0;
                totalModel.InstituNum_City = 0;
                totalModel.InstituNum_Area = 0;
                totalModel.InstituNum_town = 0;
                totalModel.PeopleNum = 0;
                totalModel.PeopleNum_City = 0;
                totalModel.PeopleNum_County = 0;
                totalModel.PeopleNum_man = 0;
                totalModel.PeopleNum_woman = 0;
                totalModel.PeopleNum_age1 = 0;
                totalModel.PeopleNum_age2 = 0;
                totalModel.PeopleNum_age3 = 0;
                totalModel.PeopleNum_age4 = 0;
                totalModel.PeopleNum_xlss = 0;
                totalModel.PeopleNum_xlbk = 0;
                totalModel.PeopleNum_xldz = 0;
                totalModel.PeopleNum_xlzz = 0;
                totalModel.PeopleNum_xlqt = 0;
                totalModel.PeopleNum_zczgj = 0;
                totalModel.PeopleNum_zcfgj = 0;
                totalModel.PeopleNum_zczj = 0;
                totalModel.PeopleNum_zccj = 0;
                totalModel.PeopleNum_zcqt = 0;
                foreach (string code in citylist)
                {
                    PromotionStatisModel model = getProvinStatisList(code, FillInYear);
                    if (model != null)
                    {
                        //int len = templist.Count;
                        //templist[len - 1].AreaName = areaServices.GetByCodeModel(code).model.Name;
                        //templist[len - 1].tag = 1;
                        model.AreaName = areaServices.GetByCodeModel(code).model.FullName;
                        totalModel.InstituNum += model.InstituNum;
                        totalModel.InstituNum_City += model.InstituNum_City;
                        totalModel.InstituNum_Area += model.InstituNum_Area;
                        totalModel.InstituNum_town += model.InstituNum_town;
                        totalModel.PeopleNum += model.PeopleNum;
                        totalModel.PeopleNum_City += model.PeopleNum_City;
                        totalModel.PeopleNum_County += model.PeopleNum_County;
                        totalModel.PeopleNum_man += model.PeopleNum_man;
                        totalModel.PeopleNum_woman += model.PeopleNum_woman;
                        totalModel.PeopleNum_age1 += model.PeopleNum_age1;
                        totalModel.PeopleNum_age2 += model.PeopleNum_age2;
                        totalModel.PeopleNum_age3 += model.PeopleNum_age3;
                        totalModel.PeopleNum_age4 += model.PeopleNum_age4;
                        totalModel.PeopleNum_xlss += model.PeopleNum_xlss;
                        totalModel.PeopleNum_xlbk += model.PeopleNum_xlbk;
                        totalModel.PeopleNum_xldz += model.PeopleNum_xldz;
                        totalModel.PeopleNum_xlzz += model.PeopleNum_xlzz;
                        totalModel.PeopleNum_xlqt += model.PeopleNum_xlqt;
                        totalModel.PeopleNum_zczgj += model.PeopleNum_zczgj;
                        totalModel.PeopleNum_zcfgj += model.PeopleNum_zcfgj;
                        totalModel.PeopleNum_zczj += model.PeopleNum_zczj;
                        totalModel.PeopleNum_zccj += model.PeopleNum_zccj;
                        totalModel.PeopleNum_zcqt += model.PeopleNum_zcqt;
                        list.Add(model);
                    }
                }
                list.Add(totalModel);
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getStatisList(AreaCode, FillInYear);
                    int len = list.Count;
                    if (len != 0)
                    {
                        list[len - 1].AreaName = areaServices.GetByCodeModel(AreaCode).model.FullName; 
                    }
                    //  list[len - 1].tag = 1;
                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear);
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }

        public PromotionStatisModel getProvinStatisList(string AreaCode, string FillInYear)
        {
            PromotionStatisModel list = new PromotionStatisModel();
            string strSql = "select ";
            strSql += "isnull(sum(t3.InstituNum),0.00) InstituNum ,";
            strSql += "isnull(sum(t3.InstituNum_City),0.00) InstituNum_City ,";
            strSql += "isnull(sum(t3.InstituNum_Area),0.00) InstituNum_Area ,";
            strSql += "isnull(sum(t3.InstituNum_town),0.00) InstituNum_town ,";
            strSql += "isnull(sum(t3.PeopleNum),0.00) PeopleNum,";
            strSql += "isnull(sum(t3.PeopleNum_City),0.00) PeopleNum_City,";
            strSql += "isnull(sum(t3.PeopleNum_County),0.00) PeopleNum_County,";
            strSql += "isnull(sum(t3.PeopleNum_man),0.00) PeopleNum_man ,";
            strSql += "isnull(sum(t3.PeopleNum_woman),0.00) PeopleNum_woman ,";
            strSql += "isnull(sum(t3.PeopleNum_age1),0.00) PeopleNum_age1 ,";
            strSql += "isnull(sum(t3.PeopleNum_age2),0.00) PeopleNum_age2 ,";
            strSql += "isnull(sum(t3.PeopleNum_age3),0.00) PeopleNum_age3 ,";
            strSql += "isnull(sum(t3.PeopleNum_age4),0.00) PeopleNum_age4 ,";
            strSql += "isnull(sum(t3.PeopleNum_xlss),0.00) PeopleNum_xlss ,";
            strSql += "isnull(sum(t3.PeopleNum_xlbk),0.00) PeopleNum_xlbk ,";
            strSql += "isnull(sum(t3.PeopleNum_xldz),0.00) PeopleNum_xldz ,";
            strSql += "isnull(sum(t3.PeopleNum_xlzz),0.00) PeopleNum_xlzz ,";
            strSql += "isnull(sum(t3.PeopleNum_xlqt),0.00) PeopleNum_xlqt ,";
            strSql += "isnull(sum(t3.PeopleNum_zczgj),0.00) PeopleNum_zczgj ,";
            strSql += "isnull(sum(t3.PeopleNum_zcfgj),0.00) PeopleNum_zcfgj ,";
            strSql += "isnull(sum(t3.PeopleNum_zczj),0.00) PeopleNum_zczj ,";
            strSql += "isnull(sum(t3.PeopleNum_zccj),0.00) PeopleNum_zccj ,";
            strSql += "isnull(sum(t3.PeopleNum_zcqt),0.00) PeopleNum_zcqt";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code";
            strSql += " left join  T_PotPromotion t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotPromotion'  and t2.FillInYear = @FillInYear ";
            strSql += " and (t1.ParentCode = @AreaCode or t1.Code = @AreaCode) ";//加入州级人员表的数据
            strSql += "  and t2.Status = '4' ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<PromotionStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯机构人员统计列表信息失败", ex);
            }
            return list;
        }

        public List<PromotionStatisModel> getStatisList(string AreaCode, string FillInYear)
        {
            List<PromotionStatisModel> list = new List<PromotionStatisModel>();
            string strSql = "select t.FullName AreaName,tt.* from (";
            strSql += "select case when grouping(t1.Code)=1 then '" + "999" + "' else Code end AreaCode,";
            strSql += "isnull(sum(t3.InstituNum),0.00) InstituNum ,";
            strSql += "isnull(sum(t3.InstituNum_City),0.00) InstituNum_City ,";
            strSql += "isnull(sum(t3.InstituNum_Area),0.00) InstituNum_Area ,";
            strSql += "isnull(sum(t3.InstituNum_town),0.00) InstituNum_town ,";
            strSql += "isnull(sum(t3.PeopleNum),0.00) PeopleNum,";
            strSql += "isnull(sum(t3.PeopleNum_City),0.00) PeopleNum_City,";
            strSql += "isnull(sum(t3.PeopleNum_County),0.00) PeopleNum_County,";
            strSql += "isnull(sum(t3.PeopleNum_man),0.00) PeopleNum_man ,";
            strSql += "isnull(sum(t3.PeopleNum_woman),0.00) PeopleNum_woman ,";
            strSql += "isnull(sum(t3.PeopleNum_age1),0.00) PeopleNum_age1 ,";
            strSql += "isnull(sum(t3.PeopleNum_age2),0.00) PeopleNum_age2 ,";
            strSql += "isnull(sum(t3.PeopleNum_age3),0.00) PeopleNum_age3 ,";
            strSql += "isnull(sum(t3.PeopleNum_age4),0.00) PeopleNum_age4 ,";
            strSql += "isnull(sum(t3.PeopleNum_xlss),0.00) PeopleNum_xlss ,";
            strSql += "isnull(sum(t3.PeopleNum_xlbk),0.00) PeopleNum_xlbk ,";
            strSql += "isnull(sum(t3.PeopleNum_xldz),0.00) PeopleNum_xldz ,";
            strSql += "isnull(sum(t3.PeopleNum_xlzz),0.00) PeopleNum_xlzz ,";
            strSql += "isnull(sum(t3.PeopleNum_xlqt),0.00) PeopleNum_xlqt ,";
            strSql += "isnull(sum(t3.PeopleNum_zczgj),0.00) PeopleNum_zczgj ,";
            strSql += "isnull(sum(t3.PeopleNum_zcfgj),0.00) PeopleNum_zcfgj ,";
            strSql += "isnull(sum(t3.PeopleNum_zczj),0.00) PeopleNum_zczj ,";
            strSql += "isnull(sum(t3.PeopleNum_zccj),0.00) PeopleNum_zccj ,";
            strSql += "isnull(sum(t3.PeopleNum_zcqt),0.00) PeopleNum_zcqt";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code";
            strSql += " left join  T_PotPromotion t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotPromotion'  and t2.FillInYear = @FillInYear ";
            strSql += "  and (t1.ParentCode = @AreaCode  or  t1.Code = @AreaCode) ";//加入州级人员表的数据
            strSql += "  and t2.Status = '4' ";
            strSql += " group by rollup(t1.Code)";
            strSql += " )tt left join  T_Area t on t.Code = tt.AreaCode order by t.Code desc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<PromotionStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯机构人员统计列表信息失败", ex);
            }
            return list;
        }

        public List<PromotionStatisModel> getCountyStatisList(string AreaCode, string FillInYear)
        {
            List<PromotionStatisModel> list = new List<PromotionStatisModel>();
            string strSql = "select t1.FullName AreaName,";
            strSql += "isnull(t3.InstituNum,0.00) InstituNum ,";
            strSql += "isnull(t3.InstituNum_City,0.00) InstituNum_City ,";
            strSql += "isnull(t3.InstituNum_Area,0.00) InstituNum_Area ,";
            strSql += "isnull(t3.InstituNum_town,0.00) InstituNum_town ,";
            strSql += "isnull(t3.PeopleNum,0.00) PeopleNum,";
            strSql += "isnull(t3.PeopleNum_City,0.00) PeopleNum_City,";
            strSql += "isnull(t3.PeopleNum_County,0.00) PeopleNum_County,";
            strSql += "isnull(t3.PeopleNum_man,0.00) PeopleNum_man ,";
            strSql += "isnull(t3.PeopleNum_woman,0.00) PeopleNum_woman ,";
            strSql += "isnull(t3.PeopleNum_age1,0.00) PeopleNum_age1 ,";
            strSql += "isnull(t3.PeopleNum_age2,0.00) PeopleNum_age2 ,";
            strSql += "isnull(t3.PeopleNum_age3,0.00) PeopleNum_age3 ,";
            strSql += "isnull(t3.PeopleNum_age4,0.00) PeopleNum_age4 ,";
            strSql += "isnull(t3.PeopleNum_xlss,0.00) PeopleNum_xlss ,";
            strSql += "isnull(t3.PeopleNum_xlbk,0.00) PeopleNum_xlbk ,";
            strSql += "isnull(t3.PeopleNum_xldz,0.00) PeopleNum_xldz ,";
            strSql += "isnull(t3.PeopleNum_xlzz,0.00) PeopleNum_xlzz ,";
            strSql += "isnull(t3.PeopleNum_xlqt,0.00) PeopleNum_xlqt ,";
            strSql += "isnull(t3.PeopleNum_zczgj,0.00) PeopleNum_zczgj ,";
            strSql += "isnull(t3.PeopleNum_zcfgj,0.00) PeopleNum_zcfgj ,";
            strSql += "isnull(t3.PeopleNum_zczj,0.00) PeopleNum_zczj ,";
            strSql += "isnull(t3.PeopleNum_zccj,0.00) PeopleNum_zccj ,";
            strSql += "isnull(t3.PeopleNum_zcqt,0.00) PeopleNum_zcqt";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code";
            strSql += " left join  T_PotPromotion t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotPromotion' and t1.Code = @AreaCode and t2.FillInYear = @FillInYear";
            strSql += "  and t2.Status = '4' ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<PromotionStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯机构人员信息失败", ex);
            }
            return list;
        }

    }
}